package com.hnkywl.sts.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-1-13
 * Time: 下午4:20
 * To change this template use File | Settings | File Templates.
 */
public class POIUtil {
    private HSSFFont font;

    public POIUtil(HSSFWorkbook wb) {
        this.font = wb.createFont();
    }

    //设置标题样式
    public HSSFCellStyle createTitleHSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 18 加粗
        HSSFFont font = wb.createFont(); // 字体
        font.setFontHeightInPoints((short) 18);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        style.setWrapText(true);
        return style;
    }

    public HSSFCellStyle createMonthHSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置右对齐
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 10 加粗
        HSSFFont font = wb.createFont(); // 字体
        font.setFontHeightInPoints((short) 11);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        return style;
    }

    //设置标题样式
    public HSSFCellStyle createHeaderHSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 10 加粗
        HSSFFont font = wb.createFont(); // 字体
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        style.setWrapText(true); //设置自动换行
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    //设置标题样式2
    public HSSFCellStyle createHeader2HSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 10
        HSSFFont font = wb.createFont(); // 字体
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    //设置标题样式3
    public HSSFCellStyle createHeader3HSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 10
        HSSFFont font = wb.createFont(); // 字体
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        style.setWrapText(true);
        return style;
    }

    //设置内容样式2
    public HSSFCellStyle createContentHSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 9
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    //设置内容样式2
    public HSSFCellStyle createContentHSSFCellStyle2(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 9
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    //设置内容样式2
    public HSSFCellStyle createContent2HSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :宋体 8
        font.setFontHeightInPoints((short) 9);
        font.setFontName("宋体");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    //设置内容样式(库架标签)
    public HSSFCellStyle createContent3HSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :微软雅黑 8
        font.setFontHeightInPoints((short) 12);
        font.setFontName("微软雅黑");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    //设置内容样式(库架标签左对齐)
    public HSSFCellStyle createContent4HSSFCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        //设置居中对齐
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置字体样式 :微软雅黑
        font.setFontHeightInPoints((short) 9);
        font.setFontName("微软雅黑");
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        //设置边框样式
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        return style;
    }

    public float getExcelCellAutoHeight(String str, float fontCountInline) {
        float defaultRowHeight = 20.00f;//每一行的高度指定
        float defaultCount = 0.00f;
        for (int i = 0; i < str.length(); i++) {
            float ff = getregex(str.substring(i, i + 1));
            defaultCount = defaultCount + ff;
        }
        return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;//计算
    }

    //高度自适应
    public float getregex(String charStr) {

        if (charStr == " ") {
            return 0.5f;
        }
        // 判断是否为字母或字符
        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
            return 0.5f;
        }
        // 判断是否为全角

        if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
            return 1.00f;
        }
        //全角符号 及中文
        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
            return 1.00f;
        }
        return 1.00f;

    }

    public void fillRow(HSSFSheet sheet, HSSFCellStyle style, int rowIndex, Object... content) {
        fillRowWithScale(sheet, style, rowIndex, 10, content);
    }

    public void fillRowWithScale(HSSFSheet sheet, HSSFCellStyle style, int rowIndex, int scale, Object... content) {
        SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
        HSSFRow row = sheet.createRow(rowIndex);
        if (content != null) {
            for (int i = 0; i < content.length; i++) {
                HSSFCell cell = row.createCell((short) i);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                if (style != null) {
                    cell.setCellStyle(style);
                }
                if (content[i] != null) {
                    if (content[i] instanceof Date || content[i] instanceof Timestamp) {
                        cell.setCellValue(dateformat.format((Date) content[i]));
                    } else if (content[i] instanceof Number) {
                        BigDecimal b = new BigDecimal(content[i].toString());
                        Double value = b.setScale(scale, BigDecimal.ROUND_HALF_UP).doubleValue();
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(value);
                    } else {
                        cell.setCellValue(content[i].toString());
                    }
                } else {
                    cell.setCellValue("");
                }
            }
        }
    }

    public void fillRowComplex(HSSFWorkbook wb, HSSFSheet sheet, HSSFCellStyle cellStyle, int rowIndex, List<Integer> scale, Object... content) {
        SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
        HSSFRow row = sheet.createRow(rowIndex);
        if (content != null) {
            for (int i = 0; i < content.length; i++) {
                HSSFCellStyle style = cellStyle;
                if (style == null)
                    style = wb.createCellStyle();
                HSSFCell cell = row.createCell((short) i);
                cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                if (content[i] != null) {
                    if (content[i] instanceof Date || content[i] instanceof Timestamp) {
                        cell.setCellValue(dateformat.format((Date) content[i]));
                    }
                    if (content[i] instanceof Number) {
                        BigDecimal b = new BigDecimal(content[i].toString());
                        Integer s = 2;
                        if (scale != null && scale.size() > i)
                            s = scale.get(i);
                        Double value = b.setScale(s, BigDecimal.ROUND_HALF_UP).doubleValue();
                        HSSFDataFormat df = wb.createDataFormat();
                        StringBuilder format = new StringBuilder("#,##0.");
                        for (int f = 0; f < s; f++)
                            format.append("0");
                        style.setDataFormat(df.getFormat(format.toString()));
                        if (value < 0) {
                            HSSFFont font = wb.createFont();
                            font.setColor(HSSFColor.RED.index);
                            style.setFont(font);
                        }
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(value);
                    }
                    if (content[i] instanceof String) {
                        cell.setCellValue(content[i].toString());
                    }
                } else {
                    cell.setCellValue("");
                }
                cell.setCellStyle(style);
            }
        }
    }
}
